var request = require('request');
let mysql = require('mysql');
let db = require('../libs/db');
let moment = require('moment');
let data = require('../libs/data');
var shortId = require('shortid');
var utils = require('../libs/utils');
var client = require('redis').createClient();
var jwt = require("jsonwebtoken");
var Manager = db.Manager;
var XLSX = require('xlsx');
client.on('error', function (err) {
    console.log('Error ' + err);
});

let Dealer = db.Dealer;
let Code = db.code;
function formatDate(rows) {
    return rows.map(row => {
        let date = moment(row.create_time).format('YYYY-MM-DD');
        let obj = {};

        switch (row.role) {
            case 1:
                obj.role = '普通用户';
                break;
            case 2:
                obj.role = '管理员';
                break;
            case 100:
                obj.role = '超级管理员';
        }

        delete row.password;

        return Object.assign({}, row, {create_time: date}, obj);
    });
}
Date.prototype.Format = function (fmt) { //author: meizz
    var o = {
        "M+": this.getMonth() + 1, //月份
        "d+": this.getDate(), //日
        "h+": this.getHours(), //小时
        "m+": this.getMinutes(), //分
        "s+": this.getSeconds(), //秒
        "q+": Math.floor((this.getMonth() + 3) / 3), //季度
        "S": this.getMilliseconds() //毫秒
    };
    if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt;
}
module.exports = {
    // 登录
    login (req, res) {
        var name = req.body.name;
        var password = utils.getEncAse192(req.body.password,'jingzhijiuye');
        console.log('md5: ',password);
        return Manager.findOne({
            where: {
                name: name,
                password:password
            },
            attributes:['id','name']
        }).then(function (result) {
            if (result) {
                data.code = '200';
                data.msg = '查询数据成功';
                data.data = result;

                var token = jwt.sign(JSON.stringify(data.data), "secret");
                data.token = token;
                res.status(200).send(JSON.stringify(data));
            } else {
                data.code = '103';
                data.msg = '账号或密码错误';
                data.data = '';
                res.status(200).send(JSON.stringify(data));
            }
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            data.data = '';
            res.status(200).send(JSON.stringify(data));
        });
    },
    // 登录
    logins (req, res) {
        var name = req.body.name;
        var password = req.body.password;
        return Dealer.findOne({
            where: {
                phone: name,
                username:password
            },
            attributes: ['phone', 'username', 'userid']
        }).then(function (result) {
            var result = JSON.parse(JSON.stringify(result));
            if (result) {
                data.code = '200';
                data.msg = '查询数据成功';
                data.data = JSON.stringify(result);
                var token = jwt.sign(data.data, "secret");
                data.token = token;
                res.status(200).send(JSON.stringify(data));
            } else {
                data.code = '103';
                data.msg = '账号不存在';
                data.data = '';
                res.status(200).send(JSON.stringify(data));
            }
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            data.data = '';
            res.status(200).send(JSON.stringify(data));
        });
    },
    tableTotal(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var date = new Date().Format("yyyy-MM-dd");

        /*****************************************************************/
        var tag = req.body.tag;
        var tagflag = tag;
        if(tag==undefined){
            tagflag = '0';
        }
        console.log(tagflag);
        /*****************************************************************/
        client.get('tableTotal_'+tagflag+'_'+ date, function (err, object) {
            if (!err) {
                if(object != null){
                    data.code = '200';
                    data.msg = '查询数据成功';
                    data.data = JSON.parse(object);
                    res.status(200).send(JSON.stringify(data));
                }else{
                    return Manager.findOne({
                        where: {
                            name: userData.name
                        },
                        attributes:['id','name']
                    }).then(function (result) {
                        if(result){
                            var sql = 'SELECT sum(times) times, sum(total) total, sum(num) num, ( SELECT count(1) FROM jyj_usertopic a, jyj_activity b, jyj_ac c WHERE a.codeid = b.codeid AND b.acid = c.acid AND '+(tag==undefined? '1=1':'c.tag='+tag)+' ) cantimes FROM ( SELECT a.prov, a.city, a.times, b.total, b.num FROM ( SELECT a.prov, a.city, sum(1) times FROM jyj_dealer a, jyj_ac b WHERE a.userid = b.userid AND '+(tag==undefined? '1=1':'b.tag='+tag)+' GROUP BY a.prov, a.city ) a LEFT JOIN ( SELECT a.prov, a.city, sum(1) total, sum(a.isok) num FROM ( SELECT d.id, a.prov, a.city, b.tag, d.userid, CASE WHEN CONCAT( d.topic1, ",", d.topic2, ",", d.topic3 ) = ( SELECT GROUP_CONCAT(e.okanswer) okanswer FROM jyj_topic e WHERE e.codeid = c.codeid GROUP BY e.codeid ORDER BY e.id ) THEN "1" ELSE "0" END isok FROM jyj_dealer a, jyj_ac b, jyj_activity c, jyj_usertopic d WHERE a.userid = b.userid AND b.acid = c.acid AND c.codeid = d.codeid AND '+(tag==undefined? '1=1':'b.tag='+tag)+' ) a GROUP BY a.prov, a.city ) b ON a.prov = b.prov AND a.city = b.city ) a';
                            console.log('sql--',sql);
                            db.sequelize.query(sql, {
                                type: db.sequelize.QueryTypes.SELECT
                            }).then(function (result) {
                                client.set('tableTotal_'+tagflag+'_'+ date, JSON.stringify(result), function (err) {
                                    console.log(err)
                                });
                                client.expire('tableTotal_' + tagflag+'_'+ date, 3600*24);
                                data.code = '200';
                                data.msg = '查询数据成功';
                                data.data = result;
                                res.status(200).send(JSON.stringify(data));
                            })
                        }
                    }).catch(function (err) {
                        data.code = '101';
                        data.msg = '查询数据失败';
                        data.data = '';
                        res.status(200).send(JSON.stringify(data));
                    });
                }
            }else{
                console.log(err);
            }
        });
    },
    table1(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var date = new Date().Format("yyyy-MM-dd");
        /*****************************************************************/
        var tag = req.body.tag;
        var tagflag = tag;
        if(tag==undefined){
            tagflag = '0';
        }
        console.log(tagflag);
        /*****************************************************************/
        client.get('table1_' +tagflag+'_'+ date, function (err, object) {
            if (!err) {
                if (object != null) {
                    data.code = '200';
                    data.msg = '查询数据成功';
                    data.data = JSON.parse(object);
                    res.status(200).send(JSON.stringify(data));
                } else {
                    return Manager.findOne({
                        where: {
                            name: userData.name
                        },
                        attributes:['id','name']
                    }).then(function (result) {
                        if(result){
                            var sql = 'SELECT a.prov, a.city, a.times, b.total, b.num FROM ( SELECT a.prov, a.city, sum(1) times FROM jyj_dealer a, jyj_ac b WHERE a.userid = b.userid AND '+(tag==undefined? '1=1':'b.tag='+tag)+' GROUP BY a.prov, a.city ) a LEFT JOIN ( SELECT a.prov, a.city, sum(1) total, sum(a.isok) num FROM ( SELECT d.id, a.prov, a.city, b.tag, d.userid, CASE WHEN CONCAT( d.topic1, ",", d.topic2, ",", d.topic3 ) = ( SELECT GROUP_CONCAT(e.okanswer) okanswer FROM jyj_topic e WHERE e.codeid = c.codeid GROUP BY e.codeid ORDER BY e.id ) THEN "1" ELSE "0" END isok FROM jyj_dealer a, jyj_ac b, jyj_activity c, jyj_usertopic d WHERE a.userid = b.userid AND b.acid = c.acid AND c.codeid = d.codeid AND '+(tag==undefined? '1=1':'b.tag='+tag)+' ) a GROUP BY a.prov, a.city ) b ON a.prov = b.prov AND a.city = b.city';
                            db.sequelize.query(sql, {
                                type: db.sequelize.QueryTypes.SELECT
                            }).then(function (result) {
                                client.set('table1_' + tagflag+'_'+ date, JSON.stringify(result), function (err) {
                                    console.log(err)
                                });
                                client.expire('table1_' + tagflag+'_'+ date, 3600*24);
                                data.code = '200';
                                data.msg = '查询数据成功';
                                data.data = result;
                                res.status(200).send(JSON.stringify(data));
                            })
                        }
                    }).catch(function (err) {
                        data.code = '101';
                        data.msg = '查询数据失败';
                        data.data = '';
                        res.status(200).send(JSON.stringify(data));
                    });
                }
            }
        });
    },
    table1_size(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        return Manager.findOne({
            where: {
                name: userData.name
            },
            attributes:['id','name']
        }).then(function (result) {
            if(result){
                var sql = 'SELECT a.prov, a.city, a.times, b.total, b.num FROM ( SELECT a.prov, a.city, sum(1) times FROM jyj_dealer a, jyj_ac b WHERE a.userid = b.userid GROUP BY a.prov, a.city ) a LEFT JOIN ( SELECT a.prov, a.city, sum(1) total, sum(a.isok) num FROM ( SELECT d.id, a.prov, a.city, b.tag, d.userid, CASE WHEN CONCAT( d.topic1, ",", d.topic2, ",", d.topic3 ) = ( SELECT GROUP_CONCAT(e.okanswer) okanswer FROM jyj_topic e WHERE e.codeid = c.codeid GROUP BY e.codeid ORDER BY e.id ) THEN "1" ELSE "0" END isok FROM jyj_dealer a, jyj_ac b, jyj_activity c, jyj_usertopic d WHERE a.userid = b.userid AND b.acid = c.acid AND c.codeid = d.codeid ) a GROUP BY a.prov, a.city ) b ON a.prov = b.prov AND a.city = b.city';
                db.sequelize.query(sql, {
                    type: db.sequelize.QueryTypes.SELECT
                }).then(function (result) {
                    console.log(result.length);
                    data.code = '200';
                    data.msg = '查询数据成功';
                    data.data = result;
                    res.status(200).send(JSON.stringify(data));
                })
            }
        }).catch(function (err) {
            data.code = '101';
            data.msg = '查询数据失败';
            data.data = '';
            res.status(200).send(JSON.stringify(data));
        });
    },
    table2(req, res){
        var token = utils.getToken(req.headers);
        var p1 = req.body.p1;//开始时间
        var p2 = req.body.p2;//结束时间
        var p3 = req.body.p3;//分公司
        var p4 = req.body.p4;//办事处
        var userData = jwt.verify(token, 'secret');
        var date = new Date().Format("yyyy-MM-dd");
        /*****************************************************************/
        var tag = req.body.tag;
        var tagflag = tag;
        if(tag==undefined){
            tagflag = '0';
        }
        p1 = Date.parse(new Date(p1+' 00:00:00'))/1000;
        p2 = Date.parse(new Date(p2+' 00:00:00'))/1000;
        console.log('日期：',p1+'_'+p2+'_'+p3+'_'+p4);
        /*****************************************************************/
        client.get('table2_' + tagflag+'_'+ date, function (err, object) {
            if (!err) {
                if (object != null) {
                    data.code = '200';
                    data.msg = '查询数据成功';
                    var newdata = [];
                    JSON.parse(object).forEach(function (item,i) {
                        if(p1 > 0 && p2 > 0 && p1 <= item.ctime/1.0 && p2 >= item.ctime/1.0){
                            if(p3!='' && p4!='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4==''){
                                newdata.push(item);
                            }
                        }else if(p1 > 0 && isNaN(p2) && p1 <= item.ctime/1.0){
                            if(p3!='' && p4!='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4==''){
                                newdata.push(item);
                            }
                        }else if(p2 > 0 && isNaN(p1) && p2 >= item.ctime/1.0){
                            if(p3!='' && p4!='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4==''){
                                newdata.push(item);
                            }
                        }else if(isNaN(p2) && isNaN(p1)){
                            if(p3!='' && p4!='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4==''){
                                newdata.push(item);
                            }
                        }
                    });
                    if(isNaN(p1) && isNaN(p2) && p3=='' && p4==''){
                        data.data = JSON.parse(object);
                    }else{
                        data.data = newdata;
                    }
                    res.status(200).send(JSON.stringify(data));
                } else {
                    return Manager.findOne({
                        where: {
                            name: userData.name
                        },
                        attributes:['id','name']
                    }).then(function (result) {
                        if(result){
                            var sql = 'SELECT a.id, a.prov, a.city, a.shopname, a.username, a.address, a.`name`, a.area, a.tag, a.userid, DATE_FORMAT(a.createtime, "%m月%d日") createtime,unix_timestamp(a.createtime) ctime, a.isok, b.username uname, b.phone FROM ( SELECT d.id, a.prov, a.city, a.shopname, a.username, b.address, b.`name`, a.area, b.tag, d.userid, d.createtime, CASE WHEN CONCAT( d.topic1, ",", d.topic2, ",", d.topic3 ) = ( SELECT GROUP_CONCAT(e.okanswer) okanswer FROM jyj_topic e WHERE e.codeid = c.codeid GROUP BY e.codeid ORDER BY e.id ) THEN "1" ELSE "0" END isok FROM jyj_dealer a, jyj_ac b, jyj_activity c, jyj_usertopic d WHERE a.userid = b.userid AND b.acid = c.acid AND c.codeid = d.codeid and '+(tag==undefined? '1=1':'b.tag='+tag)+' order by d.id desc) a LEFT JOIN jyj_user b ON a.userid = b.userid WHERE a.isok = "1" ';
                            var where = '';
                            sql+= where + " order by a.id desc";
                            db.sequelize.query(sql, {
                                type: db.sequelize.QueryTypes.SELECT
                            }).then(function (result) {
                                client.set('table2_' + tagflag+'_'+ date, JSON.stringify(result), function (err) {
                                    console.log(err)
                                });
                                client.expire('table2_' + tagflag+'_'+ date, 3600*24);
                                data.code = '200';
                                data.msg = '查询数据成功';
                                data.data = result;
                                res.status(200).send(JSON.stringify(data));
                            })
                        }
                    }).catch(function (err) {
                        data.code = '101';
                        data.msg = '查询数据失败';
                        data.data = '';
                        res.status(200).send(JSON.stringify(data));
                    });
                }
            }
        });
    },
    table3(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var p1 = req.body.p1;
        var p2 = req.body.p2;
        var p3 = req.body.p3;
        var p4 = req.body.p4;
        var p5 = req.body.p5;
        var date = new Date().Format("yyyy-MM-dd");
        console.log('日期：',p1+'_'+p2+'_'+p3+'_'+p4+'_'+p5);
        /*****************************************************************/
        var tag = req.body.tag;
        var tagflag = tag;
        if(tag==undefined){
            tagflag = '0';
        }
        console.log(tagflag);
        p1 = Date.parse(new Date(p1+' 00:00:00'))/1000;
        p2 = Date.parse(new Date(p2+' 00:00:00'))/1000;
        console.log('日期：',p1+'_'+p2+'_'+p3+'_'+p4+'_'+p5);
        /*****************************************************************/
        client.get('table3_' + tagflag+'_'+ date, function (err, object) {
            if (!err) {
                if (object != null) {
                    data.code = '200';
                    data.msg = '查询数据成功';
                    var newdata = [];
                    JSON.parse(object).forEach(function (item,i) {
                        if(p1 > 0 && p2 > 0 && p1 <= item.ctimes/1.0 && p2 >= item.ctimes/1.0){
                            if(p3!='' && p4!='' && p5!='' && p3==item.prov && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5!='' && p3==item.prov && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4!='' && p5=='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5!='' && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5=='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5!='' && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5==''){
                                newdata.push(item);
                            }
                        }else if(p1 > 0 && isNaN(p2) && p1 <= item.ctimes/1.0){
                            if(p3!='' && p4!='' && p5!='' && p3==item.prov && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5!='' && p3==item.prov && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4!='' && p5=='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5!='' && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5=='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5!='' && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5=='' && p3==item.prov){
                                newdata.push(item);
                            }
                        }else if(p2 > 0 && isNaN(p1) && p2 >= item.ctimes/1.0){
                            if(p3!='' && p4!='' && p5!='' && p3==item.prov && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5!='' && p3==item.prov && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4!='' && p5=='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5!='' && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5=='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5!='' && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5=='' && p3==item.prov){
                                newdata.push(item);
                            }
                        }else if(isNaN(p1) && isNaN(p2)){
                            if(p3!='' && p4!='' && p5!='' && p3==item.prov && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5!='' && p3==item.prov && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4!='' && p5=='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5!='' && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5=='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5!='' && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5=='' && p3==item.prov){
                                newdata.push(item);
                            }
                        }
                    });
                    if(isNaN(p1) && isNaN(p2) && p3=='' && p4=='' && p5==''){
                        data.data = JSON.parse(object);
                    }else{
                        data.data = newdata;
                    }
                    res.status(200).send(JSON.stringify(data));
                } else {
                    return Manager.findOne({
                        where: {
                            name: userData.name
                        },
                        attributes:['id','name']
                    }).then(function (result) {
                        if(result){
                            var sql = 'SELECT a.prov, a.city, a.area, a.shopname, a.`name`, a.address, DATE_FORMAT(a.ctime, "%m月%d日") cdate,unix_timestamp(DATE_FORMAT(a.ctime, "%Y-%m-%d")) ctimes, DATE_FORMAT(a.ctime, "%H:%i") ctime, IFNULL(b.total, 0) total, IFNULL(b.num, 0) num, a.acid FROM ( SELECT a.prov, a.city, a.area, a.shopname, b.`name`, b.address, b.ctime, b.acid FROM jyj_dealer a, jyj_ac b WHERE a.userid = b.userid AND '+(tag==undefined? '1=1':'b.tag='+tag)+' GROUP BY a.prov, a.city, b.acid ) a LEFT JOIN ( SELECT a.prov, a.city, sum(1) total, sum(a.isok) num, a.acid FROM ( SELECT d.id, a.prov, a.city, b.tag, d.userid, b.acid, CASE WHEN CONCAT( d.topic1, ",", d.topic2, ",", d.topic3 ) = ( SELECT GROUP_CONCAT(e.okanswer) okanswer FROM jyj_topic e WHERE e.codeid = c.codeid GROUP BY e.codeid ORDER BY e.id ) THEN "1" ELSE "0" END isok FROM jyj_dealer a, jyj_ac b, jyj_activity c, jyj_usertopic d WHERE a.userid = b.userid AND b.acid = c.acid AND c.codeid = d.codeid AND '+(tag==undefined? '1=1':'b.tag='+tag)+' ) a GROUP BY a.prov, a.city, a.acid ) b ON a.acid = b.acid WHERE 1 = 1 ';
                            var where = '';
                            sql+= where + " order by a.ctime desc";
                            db.sequelize.query(sql, {
                                type: db.sequelize.QueryTypes.SELECT
                            }).then(function (result) {
                                client.set('table3_' + tagflag+'_'+ date, JSON.stringify(result), function (err) {
                                    console.log(err)
                                });
                                client.expire('table3_' + tagflag+'_'+ date, 3600*24);
                                data.code = '200';
                                data.msg = '查询数据成功';
                                data.data = result;
                                res.status(200).send(JSON.stringify(data));
                            })
                        }
                    }).catch(function (err) {
                        data.code = '101';
                        data.msg = '查询数据失败';
                        data.data = '';
                        res.status(200).send(JSON.stringify(data));
                    });
                }
            }
        });
    },
    table4(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var p1 = req.body.p1;
        var p2 = req.body.p2;
        var date = new Date().Format("yyyy-MM-dd");
        p1 = Date.parse(new Date(p1+' 00:00:00'))/1000;
        p2 = Date.parse(new Date(p2+' 00:00:00'))/1000;
        client.get('table4_' + date, function (err, object) {
            if (!err) {
                if (object != null) {
                    data.code = '200';
                    data.msg = '查询数据成功';
                    var newdata = [];
                    JSON.parse(object).forEach(function (item,i) {
                        if(p1 > 0 && p2 > 0 && p1 <= item.ctime/1.0 && p2 >= item.ctime/1.0){
                            newdata.push(item);
                        }else if(p1 > 0 && isNaN(p2) && p1 <= item.ctime/1.0){
                            newdata.push(item);
                        }else if(p2 > 0 && isNaN(p1) && p2 >= item.ctime/1.0){
                            newdata.push(item);
                        }
                    });
                    if(isNaN(p1) && isNaN(p2)){
                        data.data = JSON.parse(object);
                    }else{
                        data.data = newdata;
                    }
                    res.status(200).send(JSON.stringify(data));
                } else {
                    return Manager.findOne({
                        where: {
                            name: userData.name
                        },
                        attributes:['id','name']
                    }).then(function (result) {
                        if(result){
                            var sql = 'select DATE_FORMAT(a.createtime,"%Y-%m-%d") createtime,unix_timestamp(DATE_FORMAT(a.createtime, "%Y-%m-%d")) ctime,sum(1) "num" from jyj_user a where 1=1  ';
                            var where = '';
                            sql+= where +' GROUP BY DATE_FORMAT(a.createtime,"%Y-%m-%d")';
                            db.sequelize.query(sql, {
                                type: db.sequelize.QueryTypes.SELECT
                            }).then(function (result) {
                                client.set('table4_' + date, JSON.stringify(result), function (err) {
                                    console.log(err)
                                });
                                client.expire('table4_' + date, 3600*24);
                                data.code = '200';
                                data.msg = '查询数据成功';
                                data.data = result;
                                res.status(200).send(JSON.stringify(data));
                            })
                        }
                    }).catch(function (err) {
                        data.code = '101';
                        data.msg = '查询数据失败';
                        data.data = '';
                        res.status(200).send(JSON.stringify(data));
                    });
                }
            }
        });
    },
    exportData1(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var date = new Date().Format("yyyy-MM-dd");
        /*****************************************************************/
        var tag = req.body.tag;
        var tagflag = tag;
        if(tag==undefined){
            tagflag = '0';
        }
        console.log(tagflag);
        /*****************************************************************/
        client.get('table1_' +tagflag+'_'+ date, function (err, object) {
            if (!err) {
                if (object != null) {
                    var result = JSON.parse(object);
                    console.log(result.length);
                    var _headers = ['prov', 'city', 'times', 'total', 'num']
                    var _data = result;

                    if (result.length > 0) {
                        var headers = {
                            A1: {v: '分公司'},
                            B1: {v: '办事处'},
                            C1: {v: '举办活动数'},
                            D1: {v: '参赛人数'},
                            E1: {v: '晋级人数'}
                        };
                        var datas = _data
                            .map((v, i) => _headers.map((k, j) => Object.assign({}, {
                                v: v[k],
                                position: String.fromCharCode(65 + j) + (i + 2)
                            })))
                            .reduce((prev, next) => prev.concat(next))
                            .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});

                        // 合并 headers 和 data
                        var output = Object.assign({}, headers, datas);
                        // 获取所有单元格的位置
                        var outputPos = Object.keys(output);
                        // 计算出范围
                        var ref = outputPos[0] + ':' + outputPos[outputPos.length - 1];

                        // 构建 workbook 对象
                        var wb = {
                            SheetNames: ['mySheet'],
                            Sheets: {
                                'mySheet': Object.assign({}, output, {'!ref': ref})
                            }
                        };
                        // 导出 Excel
                        XLSX.writeFile(wb, '/var/www/html/jyjhuodong/public/table1.xlsx');
                        data.code = '200';
                        data.msg = '导出数据成功';
                        res.status(200).send(JSON.stringify(data));
                    } else {
                        data.code = '101';
                        data.msg = '导出数据失败';
                        res.status(200).send(JSON.stringify(data));
                    }
                }
            }
        });
    },
    exportData2(req, res){
        var token = utils.getToken(req.headers);
        var p1 = req.body.p1;//开始时间
        var p2 = req.body.p2;//结束时间
        var p3 = req.body.p3;//分公司
        var p4 = req.body.p4;//办事处
        var userData = jwt.verify(token, 'secret');
        var date = new Date().Format("yyyy-MM-dd");
        /*****************************************************************/
        var tag = req.body.tag;
        var tagflag = tag;
        if(tag==undefined){
            tagflag = '0';
        }
        p1 = Date.parse(new Date(p1+' 00:00:00'))/1000;
        p2 = Date.parse(new Date(p2+' 00:00:00'))/1000;
        console.log('日期：',p1+'_'+p2+'_'+p3+'_'+p4);
        /*****************************************************************/
        client.get('table2_' + tagflag+'_'+ date, function (err, object) {
            if (!err) {
                if (object != null) {
                    var newdata = [];
                    var result = [];
                    JSON.parse(object).forEach(function (item,i) {
                        if(p1 > 0 && p2 > 0 && p1 <= item.ctime/1.0 && p2 >= item.ctime/1.0){
                            if(p3!='' && p4!='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4==''){
                                newdata.push(item);
                            }
                        }else if(p1 > 0 && isNaN(p2) && p1 <= item.ctime/1.0){
                            if(p3!='' && p4!='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4==''){
                                newdata.push(item);
                            }
                        }else if(p2 > 0 && isNaN(p1) && p2 >= item.ctime/1.0){
                            if(p3!='' && p4!='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4==''){
                                newdata.push(item);
                            }
                        }
                    });
                    if(isNaN(p1) && isNaN(p2) && p3=='' && p4==''){
                        result = JSON.parse(object);
                    }else{
                        result = newdata;
                    }

                    if(result.length <=1000){
                        var _headers = ['createtime', 'prov', 'city', 'shopname', 'username', 'name', 'address', 'uname', 'phone']
                        var _data = result;

                        if (result.length > 0) {
                            var headers = {
                                A1: {v: '日期'},
                                B1: {v: '分公司'},
                                C1: {v: '办事处'},
                                D1: {v: '经销商'},
                                E1: {v: '活动发起者姓名'},
                                F1: {v: '联盟店'},
                                G1: {v: '举办地点'},
                                H1: {v: '晋级者姓名'},
                                I1: {v: '晋级者电话'}
                            };
                            var datas = _data
                                .map((v, i) => _headers.map((k, j) => Object.assign({}, {
                                    v: v[k],
                                    position: String.fromCharCode(65 + j) + (i + 2)
                                })))
                                .reduce((prev, next) => prev.concat(next))
                                .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});

                            // 合并 headers 和 data
                            var output = Object.assign({}, headers, datas);
                            // 获取所有单元格的位置
                            var outputPos = Object.keys(output);
                            // 计算出范围
                            var ref = outputPos[0] + ':' + outputPos[outputPos.length - 1];

                            // 构建 workbook 对象
                            var wb = {
                                SheetNames: ['mySheet'],
                                Sheets: {
                                    'mySheet': Object.assign({}, output, {'!ref': ref})
                                }
                            };
                            // 导出 Excel
                            XLSX.writeFile(wb, '/var/www/html/jyjhuodong/public/table2.xlsx');
                            data.code = '200';
                            data.msg = '导出数据成功';
                            res.status(200).send(JSON.stringify(data));
                        } else {
                            data.code = '101';
                            data.msg = '数据为空不能导出';
                            res.status(200).send(JSON.stringify(data));
                        }
                    }else{
                        data.code = '101';
                        data.msg = '当前导出数据过多，请分批导出！';
                        res.status(200).send(JSON.stringify(data));
                    }
                }
            }
        });
    },
    exportData3(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var p1 = req.body.p1;
        var p2 = req.body.p2;
        var p3 = req.body.p3;
        var p4 = req.body.p4;
        var p5 = req.body.p5;
        var date = new Date().Format("yyyy-MM-dd");
        /*****************************************************************/
        var tag = req.body.tag;
        var tagflag = tag;
        if(tag==undefined){
            tagflag = '0';
        }
        console.log(tagflag);
        p1 = Date.parse(new Date(p1+' 00:00:00'))/1000;
        p2 = Date.parse(new Date(p2+' 00:00:00'))/1000;
        /*****************************************************************/
        client.get('table3_' + tagflag+'_'+ date, function (err, object) {
            if (!err) {
                if (object != null) {
                    var newdata = [];
                    var result = [];
                    JSON.parse(object).forEach(function (item,i) {
                        if(p1 > 0 && p2 > 0 && p1 <= item.ctimes/1.0 && p2 >= item.ctimes/1.0){
                            if(p3!='' && p4!='' && p5!='' && p3==item.prov && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5!='' && p3==item.prov && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4!='' && p5=='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5!='' && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5=='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5!='' && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5=='' && p3==item.prov){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5==''){
                                newdata.push(item);
                            }
                        }else if(p1 > 0 && isNaN(p2) && p1 <= item.ctimes/1.0){
                            if(p3!='' && p4!='' && p5!='' && p3==item.prov && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5!='' && p3==item.prov && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4!='' && p5=='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5!='' && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5=='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5!='' && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5=='' && p5==item.prov){
                                newdata.push(item);
                            }
                        }else if(p2 > 0 && isNaN(p1) && p2 >= item.ctimes/1.0){
                            if(p3!='' && p4!='' && p5!='' && p3==item.prov && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5!='' && p3==item.prov && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4!='' && p5=='' && p3==item.prov && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5!='' && p4==item.city && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3=='' && p4!='' && p5=='' && p4==item.city){
                                newdata.push(item);
                            }else if(p3=='' && p4=='' && p5!='' && p5==item.shopname){
                                newdata.push(item);
                            }else if(p3!='' && p4=='' && p5=='' && p5==item.prov){
                                newdata.push(item);
                            }
                        }
                    });
                    if(isNaN(p1) && isNaN(p2) && p3=='' && p4=='' && p5==''){
                        result = JSON.parse(object);
                    }else{
                        result = newdata;
                    }
                    var _headers = ['cdate', 'prov', 'city','area', 'shopname', 'name', 'ctime', 'total', 'num']
                    var _data = result;

                    if (result.length > 0) {
                        var headers = {
                            A1: {v: '日期'},
                            B1: {v: '分公司'},
                            C1: {v: '办事处'},
                            D1: {v: '二级区域'},
                            E1: {v: '经销商'},
                            F1: {v: '联盟店名称'},
                            G1: {v: '活动创建时间'},
                            H1: {v: '参赛人数'},
                            I1: {v: '晋级人数'}
                        };
                        var datas = _data
                            .map((v, i) => _headers.map((k, j) => Object.assign({}, {
                                v: v[k],
                                position: String.fromCharCode(65 + j) + (i + 2)
                            })))
                            .reduce((prev, next) => prev.concat(next))
                            .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});

                        // 合并 headers 和 data
                        var output = Object.assign({}, headers, datas);
                        // 获取所有单元格的位置
                        var outputPos = Object.keys(output);
                        // 计算出范围
                        var ref = outputPos[0] + ':' + outputPos[outputPos.length - 1];

                        // 构建 workbook 对象
                        var wb = {
                            SheetNames: ['mySheet'],
                            Sheets: {
                                'mySheet': Object.assign({}, output, {'!ref': ref})
                            }
                        };
                        // 导出 Excel
                        XLSX.writeFile(wb, '/var/www/html/jyjhuodong/public/table3.xlsx');
                        data.code = '200';
                        data.msg = '导出数据成功';
                        res.status(200).send(JSON.stringify(data));
                    } else {
                        data.code = '101';
                        data.msg = '导出数据失败';
                        res.status(200).send(JSON.stringify(data));
                    }
                }
            }
        });
    },
    exportData4(req, res){
        var token = utils.getToken(req.headers);
        var userData = jwt.verify(token, 'secret');
        var p1 = req.body.p1;
        var p2 = req.body.p2;
        var date = new Date().Format("yyyy-MM-dd");
        p1 = Date.parse(new Date(p1+' 00:00:00'))/1000;
        p2 = Date.parse(new Date(p2+' 00:00:00'))/1000;
        client.get('table4_' + date, function (err, object) {
            if (!err) {
                if (object != null) {
                    var newdata = [];
                    var result = [];
                    JSON.parse(object).forEach(function (item,i) {
                        if(p1 > 0 && p2 > 0 && p1 <= item.ctime/1.0 && p2 >= item.ctime/1.0){
                            newdata.push(item);
                        }else if(p1 > 0 && isNaN(p2) && p1 <= item.ctime/1.0){
                            newdata.push(item);
                        }else if(p2 > 0 && isNaN(p1) && p2 >= item.ctime/1.0){
                            newdata.push(item);
                        }
                    });
                    if(isNaN(p1) && isNaN(p2)){
                        result = JSON.parse(object);
                    }else{
                        result = newdata;
                    }
                    console.log(result.length);
                    var total = 0;
                    for(var i=0;i<result.length;i++){
                        total+= result[i].num;
                    }
                    result.push({
                        createtime:'总数',
                        num:total
                    });
                    var _headers = ['createtime', 'num']
                    var _data = result;

                    if (result.length > 0) {
                        var headers = {
                            A1: {v: '日期'},
                            B1: {v: '新增人数'},
                        };
                        var datas = _data
                            .map((v, i) => _headers.map((k, j) => Object.assign({}, {
                                v: v[k],
                                position: String.fromCharCode(65 + j) + (i + 2)
                            })))
                            .reduce((prev, next) => prev.concat(next))
                            .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});

                        // 合并 headers 和 data
                        var output = Object.assign({}, headers, datas);
                        // 获取所有单元格的位置
                        var outputPos = Object.keys(output);
                        // 计算出范围
                        var ref = outputPos[0] + ':' + outputPos[outputPos.length - 1];

                        // 构建 workbook 对象
                        var wb = {
                            SheetNames: ['mySheet'],
                            Sheets: {
                                'mySheet': Object.assign({}, output, {'!ref': ref})
                            }
                        };
                        // 导出 Excel
                        XLSX.writeFile(wb, '/var/www/html/jyjhuodong/public/table4.xlsx');
                        data.code = '200';
                        data.msg = '导出数据成功';
                        res.status(200).send(JSON.stringify(data));
                    } else {
                        data.code = '101';
                        data.msg = '导出数据失败';
                        res.status(200).send(JSON.stringify(data));
                    }
                }
            }
        });
    }


};